//
//  DBKit.m
//  nonghubao
//
//  Created by 王 文彬 on 13-1-23.
//  Copyright (c) 2013年 mypocketbaby. All rights reserved.
//

/*
 说明
 
 本类是一个用于处理数据对象与存储引擎之间的操作的中间件。
 负责处理：
    1、与存储引擎之间的连接
    2、负责管理一个唯一的上下文实例
    3、负责管理一个唯一的数据连接
    4、提供insert、select、delete、update方法作为CRUD的操作借口
 
*/

#import "DBKit.h"
#import "ManagedDBObject.h"
#import "FMDatabase.h"
#import "FMResultSet.h"
#import "Helper.h"
#import "Config.h"
#import "Option.h"
#import "Contact.h"
#import "Collage.h"
#import "Announcement.h"

@implementation DBKit


static DBKit* _DBKit;

+ (DBKit *)sharedDBKit
{
    @synchronized(self) {
        if ( ! _DBKit) {
            _DBKit = [[self alloc] init];
        }
    }
    
    return _DBKit;
}


// init
- (id)init
{
    self = [super init];
    if (self) {
        [self connect];
    }

    return self;
}

// 连接SQLite3数据库
// 连接前，进行连通性测试，如果发现未连接或连接已关闭，则重新连接
- (BOOL)connect
{
    //如果是Sqlite3，就要判断两种状态：1、是有实例；2、实例是否是活动的

        if (!_connection) {
            _connection = [FMDatabase databaseWithPath:_SYS_STORAGE_URL];
            [_connection open];
            
            NSLog(@"Opening SQLite3: %@", _SYS_STORAGE_URL);
    }

    return _connection ? true : false;
}


//
- (Option *)optionWithName:(NSString *)name
{
    Option* option;
    
    NSString* sql = [NSString stringWithFormat:@"SELECT * FROM option WHERE name='%@'", name];
    
    FMResultSet* rs = [_connection executeQuery:sql];
    while ([rs next]) {
        option = [[Option alloc] init];
        option.name = name;
        option.value = [rs stringForColumn:@"value"];
    }
    
    return option;
}

//- (NSMutableArray *)contactsWithCircleId:(NSInteger)circleId
//{
//    Contacts *contact;
//    
//    NSString* sql = [NSString stringWithFormat:@"SELECT * FROM contacts WHERE circleId='%d'", circleId];
//    
//    FMResultSet* rs = [_connection executeQuery:sql];
//    while ([rs next]) {
//        contact = [[Contacts alloc] init];
//        contact.name = name;
//        contact.mobile = [rs stringForColumn:@"mobile"];
//    }
//    
////    return contact;
//}

- (NSMutableArray *)transformToContactInfo
{
    
    NSMutableArray *contacts = [[NSMutableArray alloc] init];
    
    NSString* sql = @"SELECT * FROM contacts";
    
    FMResultSet* rs = [_connection executeQuery:sql];
    while ([rs next]) {
        
        Contact *contact = [Contact new];
        contact.name = [rs stringForColumn:@"name"];
        contact.mobile = [rs stringForColumn:@"mobile"];
        contact.status = [rs boolForColumn:@"status"];
        
        [contacts addObject:contact];
    }
    
    return contacts;
}

- (NSMutableArray *)getAnnouncementsFromDB
{
    NSMutableArray *announcements = [[NSMutableArray alloc] init];
    
    NSString* sql = [NSString stringWithFormat:@"SELECT * FROM announcement"];
    
    FMResultSet* rs = [_connection executeQuery:sql];
    while ([rs next]) {
        
        Announcement *announcement = [Announcement new];
        announcement.announcementId = [rs intForColumn:@"announcementId"];
        
        [announcements addObject:announcement];
    }
    
    return announcements;
}

- (void)deleteAnnouncement
{
    NSString* sql = [NSString stringWithFormat:@"DELETE from announcement"];
    
    [_connection executeUpdate:sql];
}

// 初始化数据库，建立Vn版本的数据表结构，并填充一些默认数据
- (void)initDB:(DBVersion)version
{
    //获取sys_has_booted（已经启动过）标记的数据。如果为nil或option.value=false，就初始化数据表
    Option* option = [self optionWithName:@"sysHasBooted"];

    if (option == nil || ![option.value boolValue]) {
        
        //初始化表时，最好按句执行
        [self executeSQL:@"CREATE TABLE option (name text PRIMARY KEY,value text)"];             //创建option表
        [self executeSQL:@"INSERT INTO option(name,value) VALUES('sysDBVersion', '1.0')"];     //初始：当前的数据库结构版本号
        [self executeSQL:@"INSERT INTO option(name,value) VALUES('sysHasBooted', '1')"];       //初始：系统是否被引导过。当本条记录建立时，值就永远为1
        [self executeSQL:@"INSERT INTO option(name,value) VALUES('sysSplashShown', '0')"];     //初始：Splash界面是否被显示过
        
        NSLog(@"db init");
    }
    
    //如果sysDBVerion=1.0，就删除contacts，并更新值为176
    option = [self optionWithName:@"sysDBVersion"];
    if ([option.value floatValue] == 1.0) {
        [self executeSQL:@"DROP TABLE contacts"];
        [self executeSQL:@"DELETE FROM option WHERE name='sysDBVersion'"];
        [self executeSQL:@"INSERT INTO option(name,value) VALUES('sysDBVersion', '176')"];
    }
    
    [self executeSQL:@"CREATE TABLE contacts (mobile text, name text, status bool, primary key(mobile,status))"];
    [self executeSQL:@"CREATE TABLE announcement (announcementId integer, primary key(announcementId))"];
    [self executeSQL:@"CREATE TABLE collages (collageId integer, name text, region text, primary key(collageId))"];
}

//
- (BOOL)executeSQL:(NSString *)sql
{
    return [_connection executeUpdate:sql];
}


//
- (FMResultSet *)querySQL:(NSString *)sql
{
    FMResultSet* rs = [_connection executeQuery:sql];
    
    return rs;
}

//
- (BOOL)save:(ManagedDBObject *)object
{
    NSString* sql;
    
    //如果是Option：由于option是键值对表，那么保存的时候，可以直接先D然后C，而不用判断是否存在
    if ([object isKindOfClass:[Option class]]) {
        Option* option = (Option *)object;

        //先删除
        [self remove:option];
        
        //再加入
        sql = [NSString stringWithFormat:@"INSERT INTO option(name,value) VALUES('%@', '%@')", option.name, option.value];
        [_connection executeUpdate:sql];
    }
    
    
    else if ([object isKindOfClass:[Contact class]])
    {
        Contact *contact = (Contact *)object;
        
        [self remove:contact];
        
        sql = [NSString stringWithFormat:@"INSERT INTO contacts(name,mobile,status) VALUES('%@', '%@', '%d')", contact.name, contact.mobile, contact.status];
        [_connection executeUpdate:sql];
    }
    
    else if ([object isKindOfClass:[Announcement class]])
    {
        Announcement *announcement = (Announcement *)object;
        
        [self remove:announcement];
        
        sql = [NSString stringWithFormat:@"INSERT INTO announcement(announcementId) VALUES('%d')",announcement.announcementId];
        [_connection executeUpdate:sql];
    }
    else if ([object isKindOfClass:[Collage class]])
    {
        Collage *collage = (Collage *)object;
//        [self remove:collage];
        sql = [NSString stringWithFormat:@"INSERT INTO collages (collageId,name,region) VALUES('%d','%@','%@')",collage.collageId, collage.name,collage.region];
        [_connection executeUpdate:sql];
    }
    else {
        
    }
    return true;
}


- (BOOL)remove:(ManagedDBObject *)object
{
    NSString* sql;
    
    //如果是Option：由于option是键值对表，那么保存的时候，可以直接先D然后C，而不用判断是否存在
    if ([object isKindOfClass:[Option class]]) {
        
        sql = [NSString stringWithFormat:@"DELETE FROM option WHERE name='%@'", [object valueForKey:@"name"]];
        [_connection executeUpdate:sql];
    }
    
    else if ([object isKindOfClass:[Contact class]])
    {
        sql = [NSString stringWithFormat:@"DELETE FROM contacts WHERE mobile='%@'", [object valueForKey:@"mobile"]];
        [_connection executeUpdate:sql];
    }
    
//    else if ([object isKindOfClass:[Announcement class]])
//    {
//        sql = [NSString stringWithFormat:@"DELETE FROM announcement WHERE announcementId='%@'", [object valueForKey:@"announcementId"]];
//        [_connection executeUpdate:sql];
//    }
    return true;
}

- (void)truncateCollageTable
{
    [self executeSQL:@"delete from collages"];
}
@end


